﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Dto生成器.UI
{
    public partial class Form1 : Form
    {
        private Dictionary<string, string> _sqlTypeMap = new Dictionary<string, string>
        {

            {
                "nvarchar",
                "string"
            },

            {
                "varchar",
                "string"
            },

            {
                "nchar",
                "string"
            },

            {
                "ntext",
                "string"
            },

            {
                "text",
                "string"
            },

            {
                "char",
                "string"
            },

            {
                "xml",
                "string"
            },

            {
                "tinyint",
                "int"
            },

            {
                "smallint",
                "int"
            },

            {
                "bigint",
                "int"
            },

            {
                "bit",
                "bool"
            },

            {
                "real",
                "float"
            },

            {
                "float",
                "double"
            },

            {
                "smallmoney",
                "decimal"
            },

            {
                "money",
                "decimal"
            },

            {
                "decimal",
                "decimal"
            },

            {
                "numeric",
                "decimal"
            },

            {
                "smalldatetime",
                "DateTime"
            },

            {
                "datetime",
                "DateTime"
            },

            {
                "datetime2",
                "DateTime"
            },

            {
                "date",
                "DateTime"
            },

            {
                "time",
                "TimeSpan"
            },

            {
                "timestamp",
                "byte[]"
            },

            {
                "uniqueidentifier",
                "Guid"
            },

            {
                "binary",
                "byte[]"
            },

            {
                "varbinary",
                "byte[]"
            },

            {
                "image",
                "byte[]"
            }
        };
        private SqlDataAdapter adapter;
        private SqlCommand cmd;
        private SqlConnection con;
        public string connString = string.Empty;
        public string dateBase = "";
        private DataSet ds;
        private List<string> listDescription = new List<string>();
        private List<AttrEntity> list = new List<AttrEntity>();
        public Form1()
        {
            InitializeComponent();
            new Login().ShowDialog();
            string db = ConfigurationManager.AppSettings["DateBaseService"];
            string dataBase = ConfigurationManager.AppSettings["DateBase"];
            string userName = ConfigurationManager.AppSettings["UserName"];
            string passWord = ConfigurationManager.AppSettings["PassWord"];
            this.connString = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=false;User={2};Password={3};", new object[]
                        {
                       db,
                       dataBase,
                       userName,
                       passWord
                        });
            LoadDatabase();
            LoadDtoAttrList(new List<string>());
            this.list = new List<AttrEntity>();
            LoadDto();
        }

        #region 事件--选择数据库
        private void cmbDatabase_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.LoadTables(this.cmbDatabase.Text);
            //this.GenerateEntity();
        }
        #endregion

        #region 事件--选择表
        private void lbTables_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.GenerateEntity();
        }
        #endregion

        #region 事件--选择字段
        private void entityattr_SelectedIndexChanged(object sender, EventArgs e)
        {

            LoadDto();
        }
        #endregion

        #region 事件--清除DTO
        /// <summary>
        /// 事件--清除DTO
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void clearEntity_Click(object sender, EventArgs e)
        {
            this.list = new List<AttrEntity>();
            //LoadDto();
            this.richTextBox1.Text = "";
        }
        #endregion

        #region 事件--生成实体
        private void button1_Click(object sender, EventArgs e)
        {

        }
        #endregion

        #region 加载数据库
        /// <summary>
        /// 加载数据库
        /// </summary>
        private void LoadDatabase()
        {
            try
            {
                this.con = new SqlConnection(this.connString);
                this.con.Open();
                string cmdText = string.Format("use master;select * from sysdatabases  ORDER BY name ASC ", new object[0]);
                this.cmd = new SqlCommand(cmdText, this.con);
                this.adapter = new SqlDataAdapter(this.cmd);
                this.ds = new DataSet();
                this.adapter.Fill(this.ds);
                this.cmbDatabase.DisplayMember = "name";
                this.cmbDatabase.ValueMember = "name";
                this.cmbDatabase.DataSource = this.ds.Tables[0];
                this.adapter.Dispose();
                this.cmd.Dispose();
                this.con.Close();
                if (!string.IsNullOrEmpty(this.dateBase))
                {
                    this.cmbDatabase.Text = this.dateBase;
                    this.LoadTables(this.dateBase);
                    this.connString = Regex.Replace(this.connString, "Catalog=[0-9a-zA-Z_]+;", "Catalog=" + this.dateBase + ";");
                }
                else
                {
                    this.LoadTables(this.cmbDatabase.Text);
                    this.connString = Regex.Replace(this.connString, "Catalog=[0-9a-zA-Z_]+;", "Catalog=" + this.cmbDatabase.Text + ";");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据库加载错误！错误信息：" + ex.Message);
            }
        }
        #endregion

        #region 加载数据库中的表
        /// <summary>
        /// 加载数据库中的表
        /// </summary>
        /// <param name="database"></param>
        private void LoadTables(string database)
        {
            try
            {
                if (this.con.State != ConnectionState.Open)
                {
                    this.con.Open();
                }
                StringBuilder stringBuilder = new StringBuilder().AppendFormat("use {0};select * from sysobjects where xtype in ('U','V') AND category <> 2 ORDER BY name ASC", database);
                this.cmd = new SqlCommand(stringBuilder.ToString(), this.con);
                this.adapter = new SqlDataAdapter(this.cmd);
                this.ds = new DataSet();
                this.adapter.Fill(this.ds);
                this.lbTables.DataSource = this.ds.Tables[0];
                this.lbTables.DisplayMember = "name";
                this.lbTables.ValueMember = "name";
                this.lbTables.SelectedItems.Clear();
                this.adapter.Dispose();
                this.cmd.Dispose();
                this.con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据表加载错误！错误信息：" + ex.Message);
            }
        }
        #endregion

        #region 加载实体
        /// <summary>
        /// 加载实体
        /// </summary>
        private void GenerateEntity()
        {
            try
            {
                int num = 0;
                StringBuilder stringBuilder = new StringBuilder();
                string text = this.cmbDatabase.Text;
                string text2 = this.lbTables.Text;
                string cmdText = string.Format("USE {1};SELECT  syscolumns.name AS ColName\r\n       ,systypes.name AS TypeName\r\n       ,sys.extended_properties.value AS Description\r\n       ,sysobjects.name AS TableName\r\n       ,p2.value AS TableDescription\r\n       ,p2.*\r\nFROM    syscolumns\r\n        INNER JOIN sysobjects ON syscolumns.id = sysobjects.id\r\n        INNER JOIN systypes ON syscolumns.xtype = systypes.xtype\r\n        LEFT JOIN sys.extended_properties ON sys.extended_properties.major_id = syscolumns.id\r\n                                             AND sys.extended_properties.minor_id = syscolumns.colorder\r\n        LEFT JOIN sys.extended_properties p2 ON p2.major_id = syscolumns.id\r\n                                                AND p2.minor_id = 0 AND p2.name = 'MS_Description'\r\nWHERE   sysobjects.name = '{0}'\r\n        AND systypes.name <> 'sysname'\r\nORDER BY sys.extended_properties.minor_id ASC;", text2, text);
                this.con = new SqlConnection(this.connString);
                this.con.Open();
                this.cmd = new SqlCommand(cmdText, this.con);
                this.adapter = new SqlDataAdapter(this.cmd);
                this.ds = new DataSet();
                List<string> attrList = new List<string>();
                this.adapter.Fill(this.ds, "Entity");
                if (this.ds.Tables[0].Rows.Count == 0)
                {
                    this.txtContent.Text = "没有查询结果......";
                }
                else
                {
                    stringBuilder.AppendLine("using System;\r\n");
                    if (!string.IsNullOrEmpty(this.txtNamespace.Text.Trim()))
                    {
                        stringBuilder.AppendLine("namespace " + this.txtNamespace.Text + "\r\n{");
                        num += 4;
                    }
                    stringBuilder.Append(new string(' ', num));
                    stringBuilder.AppendLine("/// <summary>");
                    string text3 = this.ds.Tables[0].Rows[0][4].ToString().Trim().Replace(Environment.NewLine, "");
                    if (!string.IsNullOrEmpty(text3))
                    {
                        stringBuilder.Append(new string(' ', num));
                        stringBuilder.AppendLine("/// " + text3 + "实体类");
                        this.listDescription.Add(text3 + "实体类");
                    }
                    else
                    {
                        stringBuilder.AppendLine("/// ");
                    }
                    stringBuilder.Append(new string(' ', num));
                    stringBuilder.AppendLine("/// " + string.Format("{0}.dbo.{1}", text, text2));
                    this.listDescription.Add(string.Format("{0}.dbo.{1}", text, text2));
                    stringBuilder.Append(new string(' ', num));
                    stringBuilder.AppendLine("/// </summary>");
                    stringBuilder.Append(new string(' ', num));
                    stringBuilder.AppendLine("public class " + this.ds.Tables[0].Rows[0][3].ToString() + "Entity");
                    stringBuilder.Append(new string(' ', num));
                    stringBuilder.AppendLine("{");
                    for (int i = 0; i < this.ds.Tables[0].Rows.Count; i++)
                    {
                        string text4 = this.ds.Tables[0].Rows[i][2].ToString().Trim().Replace(Environment.NewLine, "");
                        if (!string.IsNullOrEmpty(text4))
                        {
                            stringBuilder.Append(new string(' ', num + 4));
                            stringBuilder.AppendLine("/// <summary>");
                            stringBuilder.Append(new string(' ', num + 4));
                            stringBuilder.AppendLine("/// " + text4);
                            this.listDescription.Add(text4);
                            stringBuilder.Append(new string(' ', num + 4));
                            stringBuilder.AppendLine("/// </summary>");
                        }
                        stringBuilder.Append(new string(' ', num + 4));
                        stringBuilder.AppendLine(string.Concat(new string[]
                        {
                    "public ",
                    this.ChangeWords(this.ds.Tables[0].Rows[i][1].ToString()),
                    " ",
                    this.ds.Tables[0].Rows[i][0].ToString(),
                    " { get; set; }"
                        }));
                        attrList.Add(this.ds.Tables[0].Rows[i][0].ToString());
                    }
                    stringBuilder.Append(new string(' ', num));
                    stringBuilder.AppendLine("}");
                    if (!string.IsNullOrEmpty(this.txtNamespace.Text.Trim()))
                    {
                        stringBuilder.AppendLine("}");
                    }
                    string text5 = stringBuilder.ToString();
                    this.txtContent.Text = text5;
                    this.ChangeColor();
                    this.adapter.Dispose();
                    this.cmd.Dispose();
                    this.con.Close();
                    LoadDtoAttrList(attrList);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("类生成失败！错误信息：" + ex.Message);
            }
        }
        #endregion

        #region 加载实体中的属性列表
        private void LoadDtoAttrList(List<string> list)
        {
            this.entityattr.DataSource = list;
            this.entityattr.DisplayMember = "name";
            this.entityattr.SelectedItems.Clear();
        }
        #endregion

        #region 加载DTO
        private void LoadDto()
        {
            //选中的DTO属性
            var obj = this.entityattr.Text.ToString();
            ///这里有一个问题
            ///就是我不知道怎么把一些属性绑定到下拉框中，通过选择下拉框，获取属性
            ///拿到属性，就可以得到该字段的描述等信息
            ///所以我这里直接再查一下数据库拿

            StringBuilder stringBuilder = new StringBuilder();
            string text = this.cmbDatabase.Text;
            string text2 = this.lbTables.Text;

            string cmdText = string.Format("USE {1};SELECT  syscolumns.name AS ColName\r\n       ,systypes.name AS TypeName\r\n       ,sys.extended_properties.value AS Description\r\n       ,sysobjects.name AS TableName\r\n       ,p2.value AS TableDescription\r\n       ,p2.*\r\nFROM    syscolumns\r\n        INNER JOIN sysobjects ON syscolumns.id = sysobjects.id\r\n        INNER JOIN systypes ON syscolumns.xtype = systypes.xtype\r\n        LEFT JOIN sys.extended_properties ON sys.extended_properties.major_id = syscolumns.id\r\n                                             AND sys.extended_properties.minor_id = syscolumns.colorder\r\n        LEFT JOIN sys.extended_properties p2 ON p2.major_id = syscolumns.id\r\n                                                AND p2.minor_id = 0 AND p2.name = 'MS_Description'\r\nWHERE   sysobjects.name = '{0}'\r\n        AND systypes.name <> 'sysname'\r\nORDER BY sys.extended_properties.minor_id ASC;", text2, text);
            this.con = new SqlConnection(this.connString);
            this.con.Open();
            this.cmd = new SqlCommand(cmdText, this.con);
            this.adapter = new SqlDataAdapter(this.cmd);
            this.ds = new DataSet();
            List<string> attrList = new List<string>();
            this.adapter.Fill(this.ds, "Entity");

            for (int i = 0; i < this.ds.Tables[0].Rows.Count; i++)
            {
                string tempName = this.ds.Tables[0].Rows[i][0].ToString();
                string tempTypeName = this.ChangeWords(this.ds.Tables[0].Rows[i][1].ToString());
                string tempDesc = this.ds.Tables[0].Rows[i][2].ToString();
                string tempTableName = this.ds.Tables[0].Rows[i][3].ToString();
                if (tempName == obj)
                {
                    var tempEntity = this.list.FirstOrDefault(j => j.Name == tempName && j.Desc == tempDesc && j.TableName == tempTableName && j.TypeName == tempTypeName);
                    if (tempEntity == null)
                    {
                        this.list.Add(new AttrEntity()
                        {
                            IsShow = true,
                            Name = tempName,
                            TypeName = tempTypeName,
                            TableName = tempTableName,
                            Desc = tempDesc
                        });
                    }
                    else
                    {
                        tempEntity.IsShow = !tempEntity.IsShow;
                    }
                }
            }

            if (!string.IsNullOrEmpty(text) && !string.IsNullOrEmpty(text2) && list.Count > 0)
            {
                int num = 0;
                StringBuilder str = new StringBuilder();
                str.AppendLine("using System;\r\n");
                if (!string.IsNullOrEmpty(this.txtNamespace.Text.Trim()))
                {
                    str.AppendLine("namespace " + this.txtNamespace.Text + "\r\n{");
                    num += 4;
                }
                str.Append(new string(' ', num));
                str.AppendLine("/// <summary>");
                str.Append(new string(' ', num));
                str.AppendLine("/// ");
                str.Append(new string(' ', num));
                str.AppendLine("/// </summary>");
                str.Append(new string(' ', num));
                str.AppendLine("public class DTOEntity");
                str.Append(new string(' ', num));
                str.AppendLine("{");
                foreach (var item in list)
                {
                    if (item.IsShow)
                    {
                        str.Append(new string(' ', num + 4));
                        str.AppendLine("/// <summary>");
                        str.Append(new string(' ', num + 4));
                        str.AppendLine("/// " + item.Desc);
                        this.listDescription.Add(item.Desc);
                        str.Append(new string(' ', num + 4));
                        str.AppendLine("/// " + item.TableName);
                        str.Append(new string(' ', num + 4));
                        str.AppendLine("/// </summary>");
                        str.Append(new string(' ', num + 4));
                        str.AppendLine(string.Concat(new string[]
                        {
                    "public ",
                    item.TypeName,
                    " ",
                    item.Name,
                    " { get; set; }"
                        }));
                    }


                }
                str.Append(new string(' ', num));
                str.AppendLine("}");
                if (!string.IsNullOrEmpty(this.txtNamespace.Text.Trim()))
                {
                    str.AppendLine("}");
                }
                string text5 = str.ToString();
                this.richTextBox1.Text = text5;
            }
        }
        #endregion

        #region 数据库数据类型与.NET数据类型映射
        private string ChangeWords(string content)
        {
            foreach (KeyValuePair<string, string> current in this._sqlTypeMap)
            {
                content = Regex.Replace(content, current.Key, current.Value);
            }
            return content;
        }
        #endregion

        #region 展示的颜色
        private void ChangeColor()
        {
            this.txtContent.SelectionStart = 0;
            this.txtContent.SelectionLength = this.txtContent.Text.Length;
            this.txtContent.SelectionColor = Color.Black;
            if (this.listDescription.Count > 0)
            {
                //this.ChangeKeyColor(this.listDescription, Color.Green);
            }
            this.ChangeKeyColor("namespace", Color.Blue);
            this.ChangeKeyColor("public", Color.Blue);
            this.ChangeKeyColor("class", Color.Blue);
            this.ChangeKeyColor("/// <summary>", Color.Gray);
            this.ChangeKeyColor("///", Color.Gray);
            this.ChangeKeyColor("/// </summary>", Color.Gray);
            this.ChangeKeyColor("int", Color.Blue);
            this.ChangeKeyColor("double", Color.Blue);
            this.ChangeKeyColor("float", Color.Blue);
            this.ChangeKeyColor("char", Color.Blue);
            this.ChangeKeyColor("string", Color.Blue);
            this.ChangeKeyColor("bool", Color.Blue);
            this.ChangeKeyColor("decimal", Color.Blue);
            this.ChangeKeyColor("enum", Color.Blue);
            this.ChangeKeyColor("const", Color.Blue);
            this.ChangeKeyColor("struct", Color.Blue);
            this.ChangeKeyColor("DateTime", Color.CadetBlue);
            this.ChangeKeyColor("get", Color.Blue);
            this.ChangeKeyColor("set", Color.Blue);
        }

        public void ChangeKeyColor(string key, Color color)
        {
            foreach (Match match in new Regex(key).Matches(this.txtContent.Text))
            {
                this.txtContent.SelectionStart = match.Index;
                this.txtContent.SelectionLength = key.Length;
                this.txtContent.SelectionColor = color;
            }
        }

        #endregion
    }
}
